import * as XLSX from 'xlsx';
import * as ExcelJS from 'exceljs';
import { getDeviceParameterDb } from '@/lib/device-parameter-database';
import { getUserManagement } from '@/lib/user-management';
import Database from 'better-sqlite3';
import path from 'path';

export interface TemplateColumn {
  field_name: string;
  field_description: string;
  data_type: string;
  data_source: string;
  measurement_unit?: string;
  example_description?: string;
  is_required: boolean;
  field_sequence: number;
  table_type: 'naming' | 'position' | 'base_params';
}

export interface TemplateGenerationOptions {
  classificationCode: string;
  specialty?: string;
  objectCategory?: string;
  userRole: string;
  includeExamples?: boolean;
  maxRows?: number;
}

export interface PBSLevel {
  id: number;
  level1_area: string;
  level2_category: string | null;
  level3_subitem: string;
  full_path: string;
  pbs_code: string;
  created_at?: string;
}

export interface OrganizedPBSData {
  [area: string]: {
    [category: string]: {
      id: number;
      subitem: string;
      fullPath: string;
      code: string;
    }[];
  };
}

export class TemplateExportService {
  private db = getDeviceParameterDb();
  private userMgmt = getUserManagement();
  private pbsDb: Database.Database;

  constructor() {
    const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
    this.pbsDb = new Database(DB_PATH, { readonly: true });
  }

  /**
   * 获取PBS层级数据
   */
  private getPBSLevels(): PBSLevel[] {
    const query = `
      SELECT
        id,
        level1_area,
        level2_category,
        level3_subitem,
        full_path,
        pbs_code,
        created_at
      FROM pbs_levels
      ORDER BY level1_area, level2_category, level3_subitem
    `;

    try {
      const stmt = this.pbsDb.prepare(query);
      return stmt.all() as PBSLevel[];
    } catch (error) {
      console.error('获取PBS层级数据失败:', error);
      return [];
    }
  }

  /**
   * 按层级组织PBS数据
   */
  private organizePBSData(pbsLevels: PBSLevel[]): OrganizedPBSData {
    const organized: OrganizedPBSData = {};

    pbsLevels.forEach(level => {
      const area = level.level1_area;
      const category = level.level2_category || '无分项';
      const subitem = level.level3_subitem;

      if (!organized[area]) {
        organized[area] = {};
      }

      if (!organized[area][category]) {
        organized[area][category] = [];
      }

      organized[area][category].push({
        id: level.id,
        subitem: subitem,
        fullPath: level.full_path,
        code: level.pbs_code
      });
    });

    return organized;
  }

  /**
   * 生成基于角色的Excel模板
   */
  async generateRoleBasedTemplate(options: TemplateGenerationOptions): Promise<ArrayBuffer> {
    const { classificationCode, specialty, objectCategory, userRole, includeExamples = true, maxRows = 100 } = options;

    // 获取设备类型映射
    const deviceMapping = this.db.getDeviceTypeMapping(classificationCode);
    if (!deviceMapping) {
      throw new Error(`未找到分类代码 ${classificationCode} 的设备映射`);
    }

    // 获取同对象类下的所有设备类
    let deviceClassList: string[] = [];
    if (specialty && objectCategory) {
      // 直接从devices表查询同专业同对象类的所有设备名称
      const stmt = this.db['db'].prepare(`
        SELECT DISTINCT name FROM devices 
        WHERE specialty = ? AND classification_name = ?
        ORDER BY name
      `);
      const devices = stmt.all(specialty, objectCategory) as { name: string }[];
      deviceClassList = devices.map(device => device.name);
      console.log('获取设备类列表:', { specialty, objectCategory, deviceClassList });
    }

    // 获取三个表的字段
    const namingFields = this.db.getParameterTableFields(deviceMapping.naming_table);
    const positionFields = this.db.getParameterTableFields(deviceMapping.position_table);
    const paramsFields = this.db.getParameterTableFields(deviceMapping.base_params_table);

    console.log('模板导出 - 获取字段信息:', {
      classificationCode,
      deviceMapping,
      namingFields: namingFields.length,
      positionFields: positionFields.length,
      paramsFields: paramsFields.length
    });

    // 合并并标记字段来源（属性展平）
    const allFields: TemplateColumn[] = [
      ...namingFields.map(f => ({ 
        ...f, 
        table_type: 'naming' as const,
        field_description: f.example_description || f.field_name
      })),
      ...positionFields.map(f => ({ 
        ...f, 
        table_type: 'position' as const,
        field_description: f.example_description || f.field_name
      })),
      ...paramsFields.map(f => ({ 
        ...f, 
        table_type: 'base_params' as const,
        field_description: f.example_description || f.field_name
      }))
    ];

    console.log('模板导出 - 合并后字段总数:', allFields.length);

    // 根据用户角色过滤字段
    const filteredFields = this.filterFieldsByRole(allFields, userRole);

    // 按照权限排序：可编辑字段优先
    const sortedFields = this.sortFieldsByPermission(filteredFields, userRole);

    // 尝试使用ExcelJS生成带有真正下拉框的Excel文件
    try {
      const buffer = await this.createExcelJSTemplateWithDropdown(
        sortedFields,
        deviceMapping.classification_name,
        userRole,
        includeExamples,
        maxRows,
        deviceClassList
      );
      return buffer;
    } catch (error) {
      console.warn('ExcelJS生成失败，回退到XLSX方案:', error.message);

      // 回退到XLSX方案
      const workbook = this.createTemplateWorkbook(
        sortedFields,
        deviceMapping.classification_name,
        userRole,
        includeExamples,
        maxRows,
        deviceClassList
      );

      return XLSX.write(workbook, {
        type: 'array',
        bookType: 'xlsx',
        cellStyles: true,
        sheetStubs: false
      });
    }
  }

  /**
   * 根据设备映射获取设备名称
   */
  private getDeviceNameByMapping(mapping: any): string | null {
    try {
      // 通过分类码在devices表中查找设备名称
      const stmt = this.db['db'].prepare(`
        SELECT name FROM devices 
        WHERE classification_code = ?
      `);
      const device = stmt.get(mapping.classification_code) as { name: string } | undefined;
      return device?.name || null;
    } catch (error) {
      console.error('获取设备名称失败:', error);
      return null;
    }
  }

  /**
   * 根据用户角色过滤字段
   */
  private filterFieldsByRole(fields: TemplateColumn[], userRole: string): TemplateColumn[] {
    // 管理员可以看到所有字段
    if (userRole === 'A') {
      return fields;
    }

    // 根据数据来源过滤
    const allowedDataSources = this.getAllowedDataSources(userRole);
    
    return fields.filter(field => {
      // 查看者可以看到所有字段但不能编辑
      if (userRole === 'V') {
        return true;
      }
      
      // 其他角色只能看到有权限的数据源字段
      return allowedDataSources.includes(field.data_source);
    });
  }

  /**
   * 按照权限排序字段
   */
  private sortFieldsByPermission(fields: TemplateColumn[], userRole: string): TemplateColumn[] {
    const allowedDataSources = this.getAllowedDataSources(userRole);
    
    return fields.sort((a, b) => {
      // 首先按照是否可编辑排序
      const aCanEdit = userRole === 'A' || allowedDataSources.includes(a.data_source);
      const bCanEdit = userRole === 'A' || allowedDataSources.includes(b.data_source);
      
      if (aCanEdit !== bCanEdit) {
        return aCanEdit ? -1 : 1;
      }
      
      // 然后按照表类型排序（naming -> position -> base_params）
      const tableOrder = { 'naming': 0, 'position': 1, 'base_params': 2 };
      if (a.table_type !== b.table_type) {
        return tableOrder[a.table_type] - tableOrder[b.table_type];
      }
      
      // 最后按照字段序号排序
      return a.field_sequence - b.field_sequence;
    });
  }

  /**
   * 创建Excel工作簿
   */
  private createTemplateWorkbook(
    fields: TemplateColumn[],
    classificationName: string,
    userRole: string,
    includeExamples: boolean,
    maxRows: number,
    deviceClassList?: string[]
  ): XLSX.WorkBook {
    // 使用传统的XLSX方式，但添加PBS数据源工作表来支持下拉框
    const workbook = XLSX.utils.book_new();

    // 创建主数据表
    const dataSheet = this.createDataSheet(fields, includeExamples, maxRows, deviceClassList);
    XLSX.utils.book_append_sheet(workbook, dataSheet, '数据录入');

    // 创建PBS参考表（提供完整的PBS层级结构供用户参考）
    const pbsReferenceSheet = this.createPBSReferenceSheet();
    XLSX.utils.book_append_sheet(workbook, pbsReferenceSheet, 'PBS参考表');

    // 创建填写说明表
    const instructionSheet = this.createInstructionSheet();
    XLSX.utils.book_append_sheet(workbook, instructionSheet, '填写说明');

    return workbook;
  }

  /**
   * 创建数据录入表（将三表字段展平为一行）
   */
  private createDataSheet(fields: TemplateColumn[], includeExamples: boolean, maxRows: number, deviceClassList?: string[]): XLSX.WorkSheet {
    // 表头行：设备类 + PBS列 + 基础信息 + 三个表的所有字段展平
    const headers = ['设备类', 'PBS区域', 'PBS分项', 'PBS子项', '对象编号', '对象描述', ...fields.map(f => f.field_name)];

    console.log('生成Excel表头:', {
      totalColumns: headers.length,
      基础列: 6, // 设备类 + PBS区域 + PBS分项 + PBS子项 + 对象编号 + 对象描述
      PBS列: 3, // PBS区域 + PBS分项 + PBS子项
      字段列: fields.length,
      设备类数量: deviceClassList?.length || 0,
      预填行数: deviceClassList?.length || maxRows,
      设备类列表: deviceClassList || [],
      表分布: {
        命名表: fields.filter(f => f.table_type === 'naming').length,
        位置表: fields.filter(f => f.table_type === 'position').length,
        基础参数表: fields.filter(f => f.table_type === 'base_params').length
      }
    });

    // 构建工作表数据（只保留第一行表头）
    const wsData = [
      headers         // 第1行：字段名称（加粗）
    ];

    // 添加空行供用户填写（移除设备类预填充）
    const actualRows = Math.max(maxRows, deviceClassList?.length || 0, 50); // 确保有足够的行数
    for (let i = 0; i < actualRows; i++) {
      wsData.push(new Array(headers.length).fill(''));
    }

    const worksheet = XLSX.utils.aoa_to_sheet(wsData);

    // 设置列宽
    worksheet['!cols'] = [
      { wch: 20 }, // 设备类
      { wch: 18 }, // PBS区域
      { wch: 20 }, // PBS分项
      { wch: 25 }, // PBS子项
      { wch: 15 }, // 对象编号
      { wch: 20 }, // 对象描述
      ...fields.map(() => ({ wch: 15 }))
    ];

    // 设置表头加粗并居中格式
    const range = XLSX.utils.decode_range(worksheet['!ref'] || 'A1:A1');
    const headerRowIndex = 0; // 表头行是第0行
    
    // 为表头行的每个单元格设置样式
    for (let col = 0; col < headers.length; col++) {
      const cellAddress = XLSX.utils.encode_cell({ r: headerRowIndex, c: col });
      
      // 确保单元格存在
      if (!worksheet[cellAddress]) {
        worksheet[cellAddress] = { t: 's', v: headers[col] };
      }
      
      // 设置单元格样式
      worksheet[cellAddress].s = {
        font: { bold: true },
        alignment: { 
          horizontal: 'center',
          vertical: 'center'
        },
        border: {
          top: { style: 'thin' },
          bottom: { style: 'thin' },
          left: { style: 'thin' },
          right: { style: 'thin' }
        }
      };
    }

    // 添加PBS列的数据验证
    this.addPBSDataValidation(worksheet, maxRows);

    // 冻结表头行和前六列（设备类、PBS区域、PBS分项、PBS子项、对象编号、对象描述）
    worksheet['!freeze'] = { xSplit: 6, ySplit: 1 };

    return worksheet;
  }

  /**
   * 创建字段说明表
   */
  private createFieldDescriptionSheet(fields: TemplateColumn[], userRole: string): XLSX.WorkSheet {
    const allowedDataSources = this.getAllowedDataSources(userRole);

    // PBS字段说明
    const pbsFields = [
      ['PBS区域', 'PBS产品分解结构的一级区域', '文本', 'PBS', 'PBS层级表', '', '否', '是', '储罐区、汽车装卸区等'],
      ['PBS分项', 'PBS产品分解结构的二级分项', '文本', 'PBS', 'PBS层级表', '', '否', '是', '罐组、管道系统等'],
      ['PBS子项', 'PBS产品分解结构的三级子项', '文本', 'PBS', 'PBS层级表', '', '否', '是', '储罐、阀门等具体设备']
    ];

    const data = [
      ['字段名称', '字段描述', '数据类型', '数据来源', '所属表', '单位', '是否必填', '是否可编辑', '示例'],
      ...pbsFields, // 先添加PBS字段
      ...fields.map(field => [
        field.field_name,
        field.field_description,
        field.data_type,
        field.data_source,
        this.getTableDisplayName(field.table_type),
        field.measurement_unit || '',
        field.is_required ? '是' : '否',
        (userRole === 'A' || allowedDataSources.includes(field.data_source)) ? '是' : '否',
        field.example_description || ''
      ])
    ];

    const worksheet = XLSX.utils.aoa_to_sheet(data);
    
    worksheet['!cols'] = [
      { wch: 20 }, { wch: 30 }, { wch: 10 }, { wch: 10 }, { wch: 18 },
      { wch: 10 }, { wch: 10 }, { wch: 10 }, { wch: 30 }
    ];

    return worksheet;
  }

  /**
   * 创建填写说明表
   */
  private createInstructionSheet(classificationName: string, userRole: string): XLSX.WorkSheet {
    const roleDescription = this.getRoleDescription(userRole);
    
    const instructions = [
      ['石楼油库设备参数录入模板'],
      [''],
      ['设备分类', classificationName],
      ['用户角色', roleDescription],
      ['生成时间', new Date().toLocaleString('zh-CN')],
      [''],
      ['模板说明：'],
      ['本模板实现了属性展平功能，将三个参数表整合为一个Excel表格：'],
      ['• 命名信息表 - 设备编码、物资编码等标识信息'],
      ['• 位置信息表 - 安装位置、系统分区等位置信息'],  
      ['• 基础参数表 - 制造厂家、规格型号等技术参数'],
      ['所有字段展平到同一行，方便批量录入和管理。'],
      [''],
      ['填写说明：'],
      ['1. 设备类：已为对象类下的每个设备类预填一行，可直接填写其他信息'],
      ['2. PBS区域：选择设备所属的PBS一级区域（如：储罐区、汽车装卸区等）'],
      ['3. PBS分项：根据选择的区域选择对应的二级分项（如：罐组、管道系统等）'],
      ['4. PBS子项：根据区域和分项选择具体的三级子项（如：储罐、阀门等）'],
      ['5. 对象编号：设备的唯一标识符（如：P-001-001）'],
      ['6. 对象描述：设备的中文描述名称（如：离心泵#1）'],
      ['7. 属性展平：所有三个表的字段都在同一行，按表类型分组排列'],
      ['8. 字段详情：请查看"字段说明"工作表了解每个字段的详细信息'],
      ['9. 权限控制：只能编辑您有权限的字段（已按权限排序）'],
      ['10. 必填字段：请查看"字段说明"工作表确认必填字段'],
      [''],
      ['数据来源说明：'],
      ['   - P: 采购数据（采购部门负责）'],
      ['   - E: 设计数据（设计院负责）'],
      ['   - M: 监测数据（监测设备提供）'],
      ['   - C: 施工数据（施工单位负责）'],
      ['   - O: 建设数据（建设部门负责）'],
      ['   - R: 运维数据（运维部门负责）'],
      ['   - PBS: PBS层级数据（产品分解结构）'],
      [''],
      ['PBS选择说明：'],
      ['1. PBS采用三级结构：区域 → 分项 → 子项'],
      ['2. 请参考"_PBS数据源"工作表查看完整的PBS层级结构'],
      ['3. 选择时请按层级顺序：先选区域，再选分项，最后选子项'],
      ['4. 各级选择应保持逻辑一致性，确保子项属于对应的分项和区域'],
      ['5. PBS信息有助于设备的位置定位和管理分类'],
      [''],
      ['导入结果：'],
      ['展平的数据将以对象编号为主键，自动拆分并分配到对应的三个参数表中：'],
      ['• 命名信息字段 → 命名信息表'],
      ['• 位置信息字段 → 位置信息表'],
      ['• 基础参数字段 → 基础参数表'],
      [''],
      ['注意事项：'],
      ['1. 请勿修改表头结构（第1行字段名称）'],
      ['2. 设备类已预填，如需修改请保证设备类名称正确'],
      ['3. 对象编号必须唯一且不能为空'],
      ['4. 请按照字段说明工作表中的数据类型要求填写内容'],
      ['5. 导入时系统会自动拆分字段到对应表'],
      ['6. 导入时会验证权限和数据格式'],
      ['7. 有疑问请联系系统管理员']
    ];

    const worksheet = XLSX.utils.aoa_to_sheet(instructions);
    worksheet['!cols'] = [{ wch: 15 }, { wch: 50 }];

    return worksheet;
  }

  /**
   * 获取用户角色允许的数据来源
   */
  private getAllowedDataSources(userRole: string): string[] {
    switch (userRole) {
      case 'A': return ['P', 'E', 'M', 'C', 'O', 'R']; // 管理员
      case 'P': return ['P']; // 采购
      case 'E': return ['E']; // 设计
      case 'M': return ['M']; // 监测
      case 'C': return ['C']; // 施工
      case 'O': return ['O']; // 建设
      case 'R': return ['R']; // 运维
      case 'V': return []; // 查看者（不能编辑）
      default: return [];
    }
  }

  /**
   * 获取表类型中文名称
   */
  private getTableTypeName(tableType: string): string {
    switch (tableType) {
      case 'naming': return '命名信息';
      case 'position': return '位置信息';
      case 'base_params': return '基础参数';
      default: return tableType;
    }
  }

  /**
   * 获取表显示名称（用于Excel表头）
   */
  private getTableDisplayName(tableType: string): string {
    switch (tableType) {
      case 'naming': return '命名信息表';
      case 'position': return '位置信息表';
      case 'base_params': return '基础参数表';
      default: return tableType;
    }
  }

  /**
   * 为PBS列添加数据验证（使用XLSX库的数据验证格式）
   */
  private addPBSDataValidation(worksheet: XLSX.WorkSheet, maxRows: number): void {
    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);

    // 获取所有区域
    const areas = Object.keys(organizedData);

    // 获取所有分项（去重）
    const allCategories = new Set<string>();
    Object.values(organizedData).forEach(areaData => {
      Object.keys(areaData).forEach(category => {
        allCategories.add(category);
      });
    });

    // 获取所有子项（去重）
    const allSubitems = new Set<string>();
    Object.values(organizedData).forEach(areaData => {
      Object.values(areaData).forEach(categoryData => {
        categoryData.forEach(item => {
          allSubitems.add(item.subitem);
        });
      });
    });

    // 为数据行添加数据验证（从第2行开始，因为第1行是表头）
    const dataRows = Math.max(maxRows, 100); // 至少100行

    // 初始化数据验证对象
    if (!worksheet['!dataValidation']) {
      worksheet['!dataValidation'] = {};
    }

    // 为PBS区域列（B列）添加数据验证
    const areaRange = `B2:B${dataRows + 1}`;
    worksheet['!dataValidation'][areaRange] = {
      type: 'list',
      operator: 'between',
      formula1: `"${areas.join(',')}"`,
      allowBlank: true,
      showDropDown: true,
      promptTitle: 'PBS区域选择',
      prompt: '请从下拉列表中选择PBS区域',
      errorTitle: '输入错误',
      error: '请选择有效的PBS区域'
    };

    // 为PBS分项列（C列）添加数据验证
    const categoryRange = `C2:C${dataRows + 1}`;
    worksheet['!dataValidation'][categoryRange] = {
      type: 'list',
      operator: 'between',
      formula1: `"${Array.from(allCategories).join(',')}"`,
      allowBlank: true,
      showDropDown: true,
      promptTitle: 'PBS分项选择',
      prompt: '请从下拉列表中选择PBS分项',
      errorTitle: '输入错误',
      error: '请选择有效的PBS分项'
    };

    // 为PBS子项列（D列）添加数据验证
    const subitemRange = `D2:D${dataRows + 1}`;
    worksheet['!dataValidation'][subitemRange] = {
      type: 'list',
      operator: 'between',
      formula1: `"${Array.from(allSubitems).join(',')}"`,
      allowBlank: true,
      showDropDown: true,
      promptTitle: 'PBS子项选择',
      prompt: '请从下拉列表中选择PBS子项',
      errorTitle: '输入错误',
      error: '请选择有效的PBS子项'
    };
  }

  /**
   * 创建PBS参考表（提供完整的PBS层级结构）
   */
  private createPBSReferenceSheet(): XLSX.WorkSheet {
    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);

    // 创建工作表数据
    const wsData: any[][] = [];

    // 表头
    wsData.push([
      'PBS区域',
      'PBS分项',
      'PBS子项',
      '完整路径',
      '说明'
    ]);

    // 添加PBS数据
    Object.keys(organizedData).forEach(area => {
      const areaData = organizedData[area];

      Object.keys(areaData).forEach(category => {
        const categoryData = areaData[category];

        categoryData.forEach(item => {
          wsData.push([
            area,
            category === '无分项' ? '' : category,
            item.subitem,
            `${area}/${category}/${item.subitem}`,
            '请在数据录入表中选择对应的PBS层级'
          ]);
        });
      });
    });

    // 创建工作表
    const worksheet = XLSX.utils.aoa_to_sheet(wsData);

    // 设置列宽
    worksheet['!cols'] = [
      { wch: 20 }, // PBS区域
      { wch: 25 }, // PBS分项
      { wch: 30 }, // PBS子项
      { wch: 40 }, // 完整路径
      { wch: 35 }  // 说明
    ];

    return worksheet;
  }

  /**
   * 创建填写说明工作表
   */
  private createInstructionSheet(): XLSX.WorkSheet {
    const instructions = [
      ['PBS模板填写说明'],
      [''],
      ['1. PBS层级结构说明：'],
      ['   - PBS区域：项目的主要功能区域（如储罐区、汽车装卸区等）'],
      ['   - PBS分项：区域内的主要系统或设施类别（如罐组、管道系统等）'],
      ['   - PBS子项：具体的设备或构件（如储罐、输油管等）'],
      [''],
      ['2. 填写步骤：'],
      ['   - 步骤1：选择设备类型'],
      ['   - 步骤2：根据设备位置选择PBS区域'],
      ['   - 步骤3：根据设备所属系统选择PBS分项'],
      ['   - 步骤4：选择具体的PBS子项'],
      ['   - 步骤5：填写对象编号和描述'],
      ['   - 步骤6：填写其他相关字段'],
      [''],
      ['3. 注意事项：'],
      ['   - PBS三个层级必须保持逻辑一致性'],
      ['   - 可参考"PBS参考表"工作表查看完整的层级结构'],
      ['   - 对象编号应唯一，建议使用规范的编码格式'],
      ['   - 如有疑问，请联系项目管理员'],
      [''],
      ['4. 数据验证：'],
      ['   - 系统会自动验证PBS层级的有效性'],
      ['   - 无效的组合会显示错误提示'],
      ['   - 请确保所有必填字段都已填写']
    ];

    // 创建工作表
    const worksheet = XLSX.utils.aoa_to_sheet(instructions);

    // 设置列宽
    worksheet['!cols'] = [
      { wch: 60 }
    ];

    return worksheet;
  }

  /**
   * 创建PBS数据源工作表（用于下拉框引用）
   */
  private createPBSDataSourceSheet(): XLSX.WorkSheet {
    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);

    // 获取所有区域
    const areas = Object.keys(organizedData);

    // 创建工作表数据
    const wsData: any[][] = [];

    // 第一行：区域列表标题
    wsData.push(['区域列表']);

    // 第二行开始：所有区域
    areas.forEach(area => {
      wsData.push([area]);
    });

    // 空行分隔
    wsData.push([]);

    // 获取所有分项和子项
    const allCategories = new Set<string>();
    const allSubitems = new Set<string>();

    Object.values(organizedData).forEach(areaData => {
      Object.keys(areaData).forEach(category => {
        allCategories.add(category);
      });
      Object.values(areaData).forEach(categoryData => {
        categoryData.forEach(item => {
          allSubitems.add(item.subitem);
        });
      });
    });

    // 添加分项列表
    wsData.push(['分项列表']);
    Array.from(allCategories).forEach(category => {
      wsData.push([category]);
    });

    // 空行分隔
    wsData.push([]);

    // 添加子项列表
    wsData.push(['子项列表']);
    Array.from(allSubitems).forEach(subitem => {
      wsData.push([subitem]);
    });

    // 创建工作表
    const worksheet = XLSX.utils.aoa_to_sheet(wsData);

    // 设置列宽
    worksheet['!cols'] = [
      { wch: 25 }
    ];

    return worksheet;
  }

  /**
   * 原有的PBS数据源工作表方法（保留用于兼容）
   */
  private createPBSDataSheet(): XLSX.WorkSheet {
    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);

    // 获取所有区域
    const areas = Object.keys(organizedData);

    // 创建工作表数据
    const wsData: any[][] = [];

    // 第一行：区域列表标题
    wsData.push(['区域列表']);

    // 第二行开始：所有区域
    areas.forEach(area => {
      wsData.push([area]);
    });

    // 空行分隔
    wsData.push([]);

    // 为每个区域创建分项列表
    let currentRow = areas.length + 3; // 标题行 + 区域行数 + 空行
    areas.forEach((area, areaIndex) => {
      const categories = Object.keys(organizedData[area]);

      // 分项列表标题
      wsData.push([`${area}_分项列表`]);

      // 分项数据
      categories.forEach(category => {
        wsData.push([category]);
      });

      // 空行分隔（除了最后一个区域）
      if (areaIndex < areas.length - 1) {
        wsData.push([]);
      }
    });

    // 空行分隔
    wsData.push([]);
    wsData.push([]);

    // 完整PBS数据
    wsData.push(['完整PBS数据']);
    wsData.push(['ID', '区域', '分项', '子项', '完整路径', 'PBS编码']);

    pbsLevels.forEach(level => {
      wsData.push([
        level.id,
        level.level1_area,
        level.level2_category || '无分项',
        level.level3_subitem,
        level.full_path,
        level.pbs_code
      ]);
    });

    // 创建工作表
    const worksheet = XLSX.utils.aoa_to_sheet(wsData);

    // 设置列宽
    worksheet['!cols'] = [
      { wch: 20 }, // A列：区域/ID
      { wch: 20 }, // B列：区域
      { wch: 20 }, // C列：分项
      { wch: 25 }, // D列：子项
      { wch: 40 }, // E列：完整路径
      { wch: 20 }  // F列：PBS编码
    ];

    return worksheet;
  }

  /**
   * 使用ExcelJS创建带有真正下拉框的Excel模板（集成成功的PBS三级联动）
   */
  private async createExcelJSTemplateWithDropdown(
    fields: TemplateColumn[],
    classificationName: string,
    userRole: string,
    includeExamples: boolean,
    maxRows: number,
    deviceClassList?: string[]
  ): Promise<ArrayBuffer> {
    console.log('🎯 创建集成PBS三级联动的模板...');

    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);
    const areas = Object.keys(organizedData);

    console.log(`✅ 获取PBS数据成功，共 ${pbsLevels.length} 条记录`);
    console.log(`📍 PBS区域数量: ${areas.length}`);

    // 创建ExcelJS工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('数据录入');

    // 表头（保持现有结构，只添加PBS三列）
    const headers = ['设备类', 'PBS区域', 'PBS分项', 'PBS子项', '对象编号', '对象描述', ...fields.map(f => f.field_name)];

    // 添加表头行
    const headerRow = worksheet.addRow(headers);
    headerRow.font = { bold: true };
    headerRow.alignment = { horizontal: 'center', vertical: 'middle' };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    };

    // 设置列宽
    worksheet.columns = [
      { width: 20 }, // 设备类
      { width: 18 }, // PBS区域
      { width: 20 }, // PBS分项
      { width: 25 }, // PBS子项
      { width: 15 }, // 对象编号
      { width: 20 }, // 对象描述
      ...fields.map(() => ({ width: 15 }))
    ];

    // 添加数据行（支持用户配置行数，确保PBS三级联动功能）
    const dataRows = Math.max(maxRows, 200); // 至少200行，所有行都支持PBS三级联动
    for (let i = 0; i < dataRows; i++) {
      const row = worksheet.addRow(new Array(headers.length).fill(''));
      // 移除设备类预填充，改为下拉菜单
    }

    // 获取所有分项和子项
    const allCategories = new Set<string>();
    const allSubitems = new Set<string>();

    Object.values(organizedData).forEach(areaData => {
      Object.keys(areaData).forEach(category => {
        if (category && category !== '无分项') {
          allCategories.add(category);
        }
      });
      Object.values(areaData).forEach(categoryData => {
        categoryData.forEach(item => {
          allSubitems.add(item.subitem);
        });
      });
    });

    // 创建分区域数据源工作表（基于成功的方法）
    console.log('📝 创建分区域数据源工作表...');
    const areaSheets: { [key: string]: any } = {};
    areas.forEach(area => {
      const cleanAreaName = area.replace(/[\/\\\?\*\[\]]/g, '_');
      const areaSheet = workbook.addWorksheet(`数据源_${cleanAreaName}`);
      areaSheet.state = 'hidden';
      areaSheets[area] = areaSheet;

      const areaData = organizedData[area];
      const categories = Object.keys(areaData).filter(cat => cat !== '无分项');

      // A列：该区域的分项
      categories.forEach((category, index) => {
        areaSheet.getCell(`A${index + 1}`).value = category;
      });

      console.log(`  创建区域"${area}"的数据源，包含${categories.length}个分项`);
    });

    // 创建分项-子项数据源工作表（基于成功的方法）
    console.log('📝 创建分项-子项数据源工作表...');
    const categorySheets: { [key: string]: any } = {};
    areas.forEach(area => {
      const areaData = organizedData[area];
      const categories = Object.keys(areaData).filter(cat => cat !== '无分项');

      categories.forEach(category => {
        const cleanCategoryName = `${area}_${category}`.replace(/[\/\\\?\*\[\]]/g, '_');

        try {
          const categorySheet = workbook.addWorksheet(`子项源_${cleanCategoryName}`);
          categorySheet.state = 'hidden';

          const categoryData = areaData[category];
          const subitems = categoryData.map(item => item.subitem);

          // A列：该分项的所有子项
          subitems.forEach((subitem, index) => {
            categorySheet.getCell(`A${index + 1}`).value = subitem;
          });

          categorySheets[`${area}_${category}`] = categorySheet;

          console.log(`  创建分项"${area}-${category}"的子项数据源，包含${subitems.length}个子项`);

        } catch (sheetError) {
          console.warn(`  跳过分项"${area}-${category}"（工作表名称问题）:`, sheetError.message);
        }
      });
    });

    // 创建主数据源工作表
    console.log('📝 创建主数据源工作表...');
    const mainDataSheet = workbook.addWorksheet('主数据源');
    mainDataSheet.state = 'hidden';

    // A列：所有区域
    areas.forEach((area, index) => {
      mainDataSheet.getCell(`A${index + 1}`).value = area;
    });

    // B列：所有分项
    Array.from(allCategories).forEach((category, index) => {
      mainDataSheet.getCell(`B${index + 1}`).value = category;
    });

    // C列：所有子项
    Array.from(allSubitems).forEach((subitem, index) => {
      mainDataSheet.getCell(`C${index + 1}`).value = subitem;
    });

    // D列：设备类列表（新增）
    if (deviceClassList && deviceClassList.length > 0) {
      deviceClassList.forEach((deviceClass, index) => {
        mainDataSheet.getCell(`D${index + 1}`).value = deviceClass;
      });
      console.log(`✅ 设备类数据已添加到主数据源，共 ${deviceClassList.length} 个设备类`);
    }

    // 添加完整的数据验证（包括设备类和PBS三级联动）
    try {
      this.addFullCascadingValidations(worksheet, areas, allCategories, allSubitems, dataRows, deviceClassList);
    } catch (error) {
      console.warn('添加完整数据验证时出错:', error.message);
      // 回退到基础验证
      this.addBasicValidations(worksheet, areas, allCategories, allSubitems, dataRows, deviceClassList);
    }

    // 冻结表头行和前6列
    worksheet.views = [
      { state: 'frozen', xSplit: 6, ySplit: 1 }
    ];

    // 添加PBS参考表工作表
    const pbsRefSheet = workbook.addWorksheet('PBS参考表');
    this.addPBSReferenceData(pbsRefSheet, organizedData);

    // 添加填写说明工作表
    const instructionSheet = workbook.addWorksheet('填写说明');
    this.addInstructionData(instructionSheet);

    // 生成并返回ArrayBuffer（使用更兼容的选项）
    const buffer = await workbook.xlsx.writeBuffer({
      useSharedStrings: true,
      useStyles: true
    });
    return buffer as ArrayBuffer;
  }

  /**
   * 使用ExcelJS创建带有真正下拉框的Excel模板（原版本）
   */
  private async createExcelJSTemplateWorkbook(
    fields: TemplateColumn[],
    classificationName: string,
    userRole: string,
    includeExamples: boolean,
    maxRows: number,
    deviceClassList?: string[]
  ): Promise<ArrayBuffer> {
    // 获取PBS数据
    const pbsLevels = this.getPBSLevels();
    const organizedData = this.organizePBSData(pbsLevels);
    const areas = Object.keys(organizedData);

    // 创建ExcelJS工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('数据录入');

    // 表头
    const headers = ['设备类', 'PBS区域', 'PBS分项', 'PBS子项', '对象编号', '对象描述', ...fields.map(f => f.field_name)];

    // 添加表头行
    const headerRow = worksheet.addRow(headers);
    headerRow.font = { bold: true };
    headerRow.alignment = { horizontal: 'center', vertical: 'middle' };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    };

    // 设置列宽
    worksheet.columns = [
      { width: 20 }, // 设备类
      { width: 18 }, // PBS区域
      { width: 20 }, // PBS分项
      { width: 25 }, // PBS子项
      { width: 15 }, // 对象编号
      { width: 20 }, // 对象描述
      ...fields.map(() => ({ width: 15 }))
    ];

    // 添加数据行
    const dataRows = Math.max(maxRows, 50);
    for (let i = 0; i < dataRows; i++) {
      const row = worksheet.addRow(new Array(headers.length).fill(''));
      // 移除设备类预填充，改为下拉菜单
    }

    // 为设备类列添加下拉验证（A列）- 新增
    if (deviceClassList && deviceClassList.length > 0) {
      const deviceClassString = deviceClassList.join(',');
      if (deviceClassString.length < 255) { // Excel公式长度限制
        worksheet.dataValidations.add('A2:A' + (dataRows + 1), {
          type: 'list',
          allowBlank: true,
          formulae: [`"${deviceClassString}"`],
          showErrorMessage: true,
          errorTitle: '无效选择',
          error: '请选择有效的设备类'
        });
        console.log('✅ 设备类下拉验证已添加（传统XLSX方法）');
      }
    }

    // 为PBS区域列添加下拉验证（B列）
    worksheet.dataValidations.add('B2:B' + (dataRows + 1), {
      type: 'list',
      allowBlank: true,
      formulae: [`"${areas.join(',')}"`],
      showErrorMessage: true,
      errorTitle: '无效选择',
      error: '请从下拉列表中选择有效的PBS区域',
      prompt: '请选择PBS区域',
      promptTitle: 'PBS区域选择'
    });

    // 获取所有分项和子项（用于基础下拉）
    const allCategories = new Set<string>();
    const allSubitems = new Set<string>();

    Object.values(organizedData).forEach(areaData => {
      Object.keys(areaData).forEach(category => {
        allCategories.add(category);
      });
      Object.values(areaData).forEach(categoryData => {
        categoryData.forEach(item => {
          allSubitems.add(item.subitem);
        });
      });
    });

    // 为PBS分项列添加下拉验证（C列）
    worksheet.dataValidations.add('C2:C' + (dataRows + 1), {
      type: 'list',
      allowBlank: true,
      formulae: [`"${Array.from(allCategories).join(',')}"`],
      showErrorMessage: true,
      errorTitle: '无效选择',
      error: '请从下拉列表中选择有效的PBS分项',
      prompt: '请选择PBS分项',
      promptTitle: 'PBS分项选择'
    });

    // 为PBS子项列添加下拉验证（D列）
    worksheet.dataValidations.add('D2:D' + (dataRows + 1), {
      type: 'list',
      allowBlank: true,
      formulae: [`"${Array.from(allSubitems).join(',')}"`],
      showErrorMessage: true,
      errorTitle: '无效选择',
      error: '请从下拉列表中选择有效的PBS子项',
      prompt: '请选择PBS子项',
      promptTitle: 'PBS子项选择'
    });

    // 冻结表头行和前6列
    worksheet.views = [
      { state: 'frozen', xSplit: 6, ySplit: 1 }
    ];

    // 生成并返回ArrayBuffer
    const buffer = await workbook.xlsx.writeBuffer();
    return buffer as ArrayBuffer;
  }

  /**
   * 为PBS参考表工作表添加数据
   */
  private addPBSReferenceData(worksheet: any, organizedData: any): void {
    // 表头
    const headers = ['PBS区域', 'PBS分项', 'PBS子项', '完整路径', '联动说明'];
    const headerRow = worksheet.addRow(headers);
    headerRow.font = { bold: true };
    headerRow.alignment = { horizontal: 'center', vertical: 'middle' };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    };

    // 添加PBS数据
    Object.keys(organizedData).forEach(area => {
      const areaData = organizedData[area];

      Object.keys(areaData).forEach(category => {
        const categoryData = areaData[category];

        categoryData.forEach(item => {
          const row = worksheet.addRow([
            area,
            category === '无分项' ? '' : category,
            item.subitem,
            `${area}/${category}/${item.subitem}`,
            '所有行都支持完整三级联动'
          ]);
        });
      });
    });

    // 设置列宽
    worksheet.columns = [
      { width: 20 }, // PBS区域
      { width: 25 }, // PBS分项
      { width: 30 }, // PBS子项
      { width: 40 }, // 完整路径
      { width: 30 }  // 联动说明
    ];
  }

  /**
   * 为填写说明工作表添加数据
   */
  private addInstructionData(worksheet: any): void {
    const instructions = [
      ['PBS模板填写说明'],
      [''],
      ['1. PBS层级结构说明：'],
      ['   - PBS区域：项目的主要功能区域（如储罐区、汽车装卸区等）'],
      ['   - PBS分项：区域内的主要系统或设施类别（如罐组、管道系统等）'],
      ['   - PBS子项：具体的设备或构件（如储罐、输油管等）'],
      [''],
      ['2. 填写步骤：'],
      ['   - 步骤1：选择设备类（有下拉框，来源于当前对象类的所有设备）'],
      ['   - 步骤2：根据设备位置选择PBS区域（有下拉框）'],
      ['   - 步骤3：根据设备所属系统选择PBS分项（会根据区域自动过滤）'],
      ['   - 步骤4：选择具体的PBS子项（会根据分项自动过滤）'],
      ['   - 步骤5：填写对象编号和描述'],
      ['   - 步骤6：填写其他相关字段'],
      [''],
      ['3. PBS三级联动功能：'],
      ['   - 所有行都支持完整的三级联动'],
      ['   - 选择PBS区域后，分项会自动过滤到该区域的分项'],
      ['   - 选择PBS分项后，子项会自动过滤到该分项的子项'],
      ['   - 实现真正的层级约束，确保数据一致性'],
      [''],
      ['4. 下拉框使用说明：'],
      ['   - 设备类列：显示当前对象类的所有设备，点击下拉选择'],
      ['   - PBS区域、分项、子项列都配置了智能下拉选择框'],
      ['   - 点击单元格会显示下拉箭头，点击可选择'],
      ['   - 输入无效值会显示错误提示'],
      ['   - 可参考"PBS参考表"查看完整层级结构'],
      [''],
      ['5. 注意事项：'],
      ['   - 必须按顺序选择：区域 → 分项 → 子项'],
      ['   - 更改区域或分项时，后续选择会自动清空'],
      ['   - PBS三个层级必须保持逻辑一致性'],
      ['   - 对象编号应唯一，建议使用规范编码'],
      ['   - 如有疑问，请联系项目管理员']
    ];

    instructions.forEach(instruction => {
      const row = worksheet.addRow(instruction);
      if (instruction[0] === 'PBS模板填写说明') {
        row.font = { bold: true, size: 14 };
      }
    });

    // 设置列宽
    worksheet.columns = [
      { width: 60 }
    ];
  }

  /**
   * 获取角色描述
   */
  private getRoleDescription(userRole: string): string {
    switch (userRole) {
      case 'A': return '系统管理员（可编辑所有字段）';
      case 'P': return '采购人员（可编辑采购数据）';
      case 'E': return '设计人员（可编辑设计数据）';
      case 'M': return '监测人员（可编辑监测数据）';
      case 'C': return '施工人员（可编辑施工数据）';
      case 'O': return '建设人员（可编辑建设数据）';
      case 'R': return '运维人员（可编辑运维数据）';
      case 'V': return '查看人员（只读权限）';
      default: return '未知角色';
    }
  }

  /**
   * 创建自动PBS三级联动数据源（完全自动化方案）
   */
  private createCascadingDataSource(dataSourceSheet: any, organizedData: any, areas: string[], allCategories: Set<string>, allSubitems: Set<string>) {
    console.log('📝 创建自动PBS三级联动数据源...');

    // 第一部分：PBS区域列表（A列）
    areas.forEach((area, index) => {
      dataSourceSheet.getCell(`A${index + 1}`).value = area;
    });

    // 第二部分：为每个区域创建分项列表（从E列开始）
    let categoryStartCol = 'E';
    const areaToColumnMap: { [key: string]: string } = {};

    areas.forEach((area, areaIndex) => {
      const areaData = organizedData[area];
      const categories = Object.keys(areaData).filter(cat => cat !== '无分项');

      const currentCol = String.fromCharCode(categoryStartCol.charCodeAt(0) + areaIndex);
      areaToColumnMap[area] = currentCol;

      // 添加该区域的分项
      categories.forEach((category, catIndex) => {
        dataSourceSheet.getCell(`${currentCol}${catIndex + 1}`).value = category;
      });

      console.log(`  区域"${area}"的分项 -> 列${currentCol} (${categories.length}个分项)`);
    });

    // 第三部分：将所有子项添加到B列（用于子项下拉）
    Array.from(allSubitems).forEach((subitem, index) => {
      dataSourceSheet.getCell(`B${index + 1}`).value = subitem;
    });

    console.log(`✅ 自动PBS三级联动数据源创建完成`);
    console.log(`   - ${areas.length} 个区域`);
    console.log(`   - ${Object.keys(areaToColumnMap).length} 个区域分项映射`);
    console.log(`   - ${allSubitems.size} 个子项`);

    return {
      areaToColumnMap,
      totalAreas: areas.length,
      totalSubitems: allSubitems.size
    };
  }

  /**
   * 添加三级联动的数据验证（利用PBS约束关系）
   */
  private addCascadingValidations(worksheet: any, workbook: any, areas: string[], allCategories: Set<string>, allSubitems: Set<string>, dataRows: number) {
    console.log('📝 添加PBS三级联动验证...');

    // 第一级：PBS区域下拉（固定列表）
    worksheet.dataValidations.add(`B2:B${dataRows + 1}`, {
      type: 'list',
      allowBlank: true,
      formulae: [`数据源!$A$1:$A$${areas.length}`],
      showInputMessage: true,
      promptTitle: 'PBS区域选择',
      prompt: '请选择PBS区域',
      showErrorMessage: true,
      errorTitle: '输入错误',
      error: '请从列表中选择有效的PBS区域'
    });

    console.log('✅ PBS区域下拉验证已添加');

    // 第二级：PBS分项下拉（根据区域动态变化）
    // 使用INDIRECT函数实现动态引用
    try {
      // 构建INDIRECT公式：根据B列的区域选择，动态引用对应的分项列
      // 公式逻辑：INDIRECT("数据源!"&CHAR(69+MATCH(B2,数据源!$A:$A,0)-1)&"1:"&CHAR(69+MATCH(B2,数据源!$A:$A,0)-1)&"20")
      const categoryFormula = 'INDIRECT("数据源!"&CHAR(69+MATCH(B2,数据源!$A:$A,0)-1)&"1:"&CHAR(69+MATCH(B2,数据源!$A:$A,0)-1)&"20")';

      worksheet.dataValidations.add(`C2:C${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [categoryFormula],
        showInputMessage: true,
        promptTitle: 'PBS分项选择',
        prompt: '请先选择PBS区域，然后选择对应的分项',
        showErrorMessage: true,
        errorTitle: '输入错误',
        error: '请从列表中选择有效的PBS分项'
      });

      console.log('✅ PBS分项联动下拉验证已添加');

    } catch (error) {
      console.warn('⚠️  PBS分项联动验证添加失败，使用简化方案:', error.message);

      // 回退方案：显示所有分项
      worksheet.dataValidations.add(`C2:C${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [Array.from(allCategories).join(',')],
        showInputMessage: true,
        promptTitle: 'PBS分项选择',
        prompt: '请选择PBS分项（请根据区域手动筛选）'
      });
    }

    // 第三级：PBS子项下拉（根据区域+分项动态变化）
    // 这个更复杂，需要根据B列和C列的组合来确定子项列
    try {
      // 由于ExcelJS对复杂INDIRECT公式的支持有限，这里先实现基础版本
      console.log('⚠️  PBS子项联动验证较复杂，当前使用简化方案');

      worksheet.dataValidations.add(`D2:D${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [Array.from(allSubitems).join(',')],
        showInputMessage: true,
        promptTitle: 'PBS子项选择',
        prompt: '请选择PBS子项（请根据分项手动筛选）',
        showErrorMessage: true,
        errorTitle: '输入错误',
        error: '请从列表中选择有效的PBS子项'
      });

    } catch (error) {
      console.warn('⚠️  PBS子项验证添加失败:', error.message);
    }

    console.log('🎯 PBS三级联动验证总结:');
    console.log('   ✅ 第一级（区域）：完全自动化');
    console.log('   ✅ 第二级（分项）：根据区域自动联动');
    console.log('   ⚠️  第三级（子项）：简化方案（显示所有选项）');
    console.log('');
    console.log('💡 用户使用说明:');
    console.log('   1. 先选择PBS区域');
    console.log('   2. 分项下拉框会自动显示该区域的分项');
    console.log('   3. 子项需要根据分项手动筛选选择');
  }

  /**
   * 添加可靠的数据验证（基于测试验证的方法）
   */
  private addReliableValidations(worksheet: any, workbook: any, areas: string[], allCategories: Set<string>, allSubitems: Set<string>, dataRows: number) {
    console.log('📝 添加可靠的PBS数据验证...');

    try {
      // 第一级：PBS区域下拉（使用最简单的字符串方式）
      const areaString = areas.join(',');
      worksheet.dataValidations.add(`B2:B${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [areaString],
        showInputMessage: true,
        promptTitle: 'PBS区域选择',
        prompt: '请选择PBS区域',
        showErrorMessage: true,
        errorTitle: '输入错误',
        error: '请从列表中选择有效的PBS区域'
      });

      console.log('✅ PBS区域下拉验证已添加');

      // 第二级：PBS分项下拉
      const categoryString = Array.from(allCategories).join(',');

      if (categoryString.length < 255) {
        // 如果字符串不太长，直接使用
        worksheet.dataValidations.add(`C2:C${dataRows + 1}`, {
          type: 'list',
          allowBlank: true,
          formulae: [categoryString],
          showInputMessage: true,
          promptTitle: 'PBS分项选择',
          prompt: '请选择PBS分项',
          showErrorMessage: true,
          errorTitle: '输入错误',
          error: '请从列表中选择有效的PBS分项'
        });

        console.log('✅ PBS分项下拉验证已添加（字符串方式）');
      } else {
        // 如果字符串太长，使用数据源工作表
        // 获取数据源工作表引用
        const dataSourceSheet = workbook.getWorksheet('数据源');
        const categoryCol = 'B';
        Array.from(allCategories).forEach((category, index) => {
          dataSourceSheet.getCell(`${categoryCol}${index + 1}`).value = category;
        });

        worksheet.dataValidations.add(`C2:C${dataRows + 1}`, {
          type: 'list',
          allowBlank: true,
          formulae: [`数据源!$${categoryCol}$1:$${categoryCol}$${allCategories.size}`],
          showInputMessage: true,
          promptTitle: 'PBS分项选择',
          prompt: '请选择PBS分项',
          showErrorMessage: true,
          errorTitle: '输入错误',
          error: '请从列表中选择有效的PBS分项'
        });

        console.log('✅ PBS分项下拉验证已添加（数据源方式）');
      }

      // 第三级：PBS子项下拉（由于数量多，使用数据源工作表）
      const dataSourceSheet = workbook.getWorksheet('数据源');
      const subitemCol = 'C';
      Array.from(allSubitems).forEach((subitem, index) => {
        dataSourceSheet.getCell(`${subitemCol}${index + 1}`).value = subitem;
      });

      worksheet.dataValidations.add(`D2:D${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [`数据源!$${subitemCol}$1:$${subitemCol}$${allSubitems.size}`],
        showInputMessage: true,
        promptTitle: 'PBS子项选择',
        prompt: '请选择PBS子项',
        showErrorMessage: true,
        errorTitle: '输入错误',
        error: '请从列表中选择有效的PBS子项'
      });

      console.log('✅ PBS子项下拉验证已添加（数据源方式）');

    } catch (error) {
      console.error('❌ 添加可靠数据验证失败:', error.message);
      throw error;
    }
  }

  /**
   * 添加完整的PBS三级联动验证（基于成功的方法）
   */
  private addFullCascadingValidations(worksheet: any, areas: string[], allCategories: Set<string>, allSubitems: Set<string>, dataRows: number, deviceClassList?: string[]) {
    console.log('📝 为所有行添加完整的数据验证（包括设备类和PBS三级联动）...');

    try {
      // 第零级：设备类下拉（所有行）- 新增
      if (deviceClassList && deviceClassList.length > 0) {
        console.log('📝 添加设备类验证（所有行）...');
        worksheet.dataValidations.add(`A2:A${dataRows + 1}`, {
          type: 'list',
          allowBlank: true,
          formulae: [`主数据源!$D$1:$D$${deviceClassList.length}`],
          showInputMessage: true,
          promptTitle: '设备类选择',
          prompt: '请选择设备类',
          showErrorMessage: true,
          errorTitle: '输入错误',
          error: '请从列表中选择有效的设备类'
        });

        console.log('✅ 设备类下拉验证已添加（所有行）');
      }

      // 第一级：PBS区域下拉（所有行）
      console.log('📝 添加PBS区域验证（所有行）...');
      worksheet.dataValidations.add(`B2:B${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [`主数据源!$A$1:$A$${areas.length}`],
        showInputMessage: true,
        promptTitle: 'PBS区域选择',
        prompt: '请选择PBS区域',
        showErrorMessage: true,
        errorTitle: '输入错误',
        error: '请从列表中选择有效的PBS区域'
      });

      console.log('✅ PBS区域下拉验证已添加（所有行）');

      // 第二级：PBS分项下拉（所有行，使用成功的INDIRECT方法）
      console.log('📝 添加PBS分项联动验证（所有行）...');

      // 分批处理，避免一次性处理过多行导致性能问题
      const batchSize = 50;
      const totalBatches = Math.ceil(dataRows / batchSize);

      for (let batch = 0; batch < totalBatches; batch++) {
        const startRow = 2 + batch * batchSize;
        const endRow = Math.min(startRow + batchSize - 1, dataRows + 1);

        console.log(`  处理第${batch + 1}批：第${startRow}行到第${endRow}行`);

        for (let row = startRow; row <= endRow; row++) {
          try {
            const rowFormula = `INDIRECT("数据源_"&SUBSTITUTE(B${row}," ","_")&"!$A:$A")`;

            worksheet.dataValidations.add(`C${row}`, {
              type: 'list',
              allowBlank: true,
              formulae: [rowFormula],
              showInputMessage: true,
              promptTitle: 'PBS分项选择',
              prompt: '请先选择PBS区域',
              showErrorMessage: true,
              errorTitle: '输入错误',
              error: '请选择有效的PBS分项'
            });

          } catch (rowError) {
            console.warn(`    第${row}行分项验证设置失败:`, rowError.message);

            // 回退方案：使用所有分项
            worksheet.dataValidations.add(`C${row}`, {
              type: 'list',
              allowBlank: true,
              formulae: [`主数据源!$B$1:$B$${allCategories.size}`],
              showInputMessage: true,
              promptTitle: 'PBS分项选择',
              prompt: '请选择PBS分项'
            });
          }
        }
      }

      console.log('✅ PBS分项联动验证已添加（所有行）');

      // 第三级：PBS子项下拉（所有行，使用成功的INDIRECT方法）
      console.log('📝 添加PBS子项联动验证（所有行）...');

      for (let batch = 0; batch < totalBatches; batch++) {
        const startRow = 2 + batch * batchSize;
        const endRow = Math.min(startRow + batchSize - 1, dataRows + 1);

        console.log(`  处理第${batch + 1}批：第${startRow}行到第${endRow}行`);

        for (let row = startRow; row <= endRow; row++) {
          try {
            const subitemFormula = `INDIRECT("子项源_"&SUBSTITUTE(B${row}," ","_")&"_"&SUBSTITUTE(C${row}," ","_")&"!$A:$A")`;

            worksheet.dataValidations.add(`D${row}`, {
              type: 'list',
              allowBlank: true,
              formulae: [subitemFormula],
              showInputMessage: true,
              promptTitle: 'PBS子项选择',
              prompt: '请先选择PBS区域和分项',
              showErrorMessage: true,
              errorTitle: '输入错误',
              error: '请选择有效的PBS子项'
            });

          } catch (subitemError) {
            console.warn(`    第${row}行子项验证设置失败:`, subitemError.message);

            // 回退方案：使用所有子项
            worksheet.dataValidations.add(`D${row}`, {
              type: 'list',
              allowBlank: true,
              formulae: [`主数据源!$C$1:$C$${allSubitems.size}`],
              showInputMessage: true,
              promptTitle: 'PBS子项选择',
              prompt: '请选择PBS子项'
            });
          }
        }
      }

      console.log('✅ PBS子项联动验证已添加（所有行）');

    } catch (error) {
      console.error('❌ 添加PBS三级联动验证失败:', error.message);
      throw error;
    }
  }

  /**
   * 添加最基础的数据验证（最后的回退方案）
   */
  private addBasicValidations(worksheet: any, areas: string[], allCategories: Set<string>, allSubitems: Set<string>, dataRows: number, deviceClassList?: string[]) {
    console.log('📝 使用最基础的数据验证方案...');

    try {
      // 添加设备类下拉（如果有设备类列表）
      if (deviceClassList && deviceClassList.length > 0) {
        const deviceClassString = deviceClassList.join(',');
        if (deviceClassString.length < 255) { // Excel公式长度限制
          worksheet.dataValidations.add(`A2:A${dataRows + 1}`, {
            type: 'list',
            allowBlank: true,
            formulae: [deviceClassString],
            showInputMessage: true,
            promptTitle: '设备类选择',
            prompt: '请选择设备类'
          });
          console.log('✅ 基础设备类下拉验证已添加');
        }
      }

      // 添加PBS区域下拉
      const areaString = areas.join(',');
      worksheet.dataValidations.add(`B2:B${dataRows + 1}`, {
        type: 'list',
        allowBlank: true,
        formulae: [areaString],
        showInputMessage: true,
        promptTitle: 'PBS区域选择',
        prompt: '请选择PBS区域'
      });

      console.log('✅ 基础PBS区域下拉验证已添加');
      console.log('⚠️  PBS分项和子项请参考PBS参考表手动填写');

    } catch (error) {
      console.warn('连基础数据验证都失败:', error.message);
    }
  }
}

// 导出单例实例
export const templateExportService = new TemplateExportService();